package cn.edu.sdjzu.xg.eagle.dao.mapper;

import cn.edu.sdjzu.xg.eagle.dao.DepartmentDao;
import cn.edu.sdjzu.xg.eagle.entity.Department;
import cn.edu.sdjzu.xg.eagle.entity.School;
import cn.edu.sdjzu.xg.eagle.exception.BaseException;
import cn.edu.sdjzu.xg.eagle.factory.ServiceFactory;
import cn.edu.sdjzu.xg.eagle.service.SchoolService;
import cn.edu.sdjzu.xg.eagle.util.JdbcHelper;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.TreeSet;

/**
 * @author tg.si@188.com
 * @project xg23
 * @date 2025/3/26
 */
public class DepartmentDaoImpl implements DepartmentDao {
    @Override
    public boolean add(Department department) throws SQLException {
        //获取数据库连接
        Connection connection = JdbcHelper.getConn();
        //准备预编译的语句
        String sql_insert = "INSERT INTO department " +
                "(description, no, remarks, school_id) VALUES (?, ?, ?, ?)";
        PreparedStatement pstmt_update =
                connection.prepareStatement(sql_insert);

        pstmt_update.setString(1, department.getDescription());
        pstmt_update.setString(2, department.getNo());
        pstmt_update.setString(3, department.getRemarks());
        pstmt_update.setInt(4, department.getSchool().getId());

        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_update.executeUpdate();

        JdbcHelper.close(pstmt_update,connection);
        return rowAffected > 0;
    }

    @Override
    public Department find(Integer id) throws SQLException, BaseException, InvocationTargetException {
        //创建Department类型的引用，暂不创建对象
        Department desiredDepartment = null;
        //获得数据库连接
        Connection connection = JdbcHelper.getConn();
        String sql_select_id = "SELECT * FROM department WHERE id = ?";
        PreparedStatement pstmt_select_id =
                connection.prepareStatement(sql_select_id);
        pstmt_select_id.setInt(1,id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        //如果表中存在id对应的记录，则获得表中的字段值，并创建对象
        if(resultSet_select_id.next()){
            String description = resultSet_select_id.getString("description");
            String no = resultSet_select_id.getString("no");
            String remarks = resultSet_select_id.getString("remarks");
            int schoolId = resultSet_select_id.getInt("school_id");
            //获得当前Department隶属的学院
            School school = ServiceFactory.getService(SchoolService.class,"SchoolServiceImpl").find(schoolId);
            desiredDepartment = new Department(id, description, no, remarks,school);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet_select_id, pstmt_select_id, connection);
        return desiredDepartment;
    }
    @Override
    public Collection<Department> findAll() throws SQLException, BaseException, InvocationTargetException {
        //创建集合类对象，用来保存所有记录代表的Department对象
        //TreeSet按compare(Object o)方法指定的顺序排序
        Collection<Department> desiredDepartments = new TreeSet<Department>();
        //获取数据库连接
        Connection connection = JdbcHelper.getConn();
        //准备预编译的语句
        String sql_select_all = "SELECT * FROM Department";
        PreparedStatement pstmt_select_all =
                connection.prepareStatement(sql_select_all);
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet_select_all = pstmt_select_all.executeQuery();
        //遍历resultSet
        while(resultSet_select_all.next()){
            //获得department表中当前记录的id字段的值
            int id = resultSet_select_all.getInt("id");
            String description = resultSet_select_all.getString("description");
            String no = resultSet_select_all.getString("no");
            String remarks = resultSet_select_all.getString("remarks");
            int schoolId = resultSet_select_all.getInt("school_id");
            //获得当前Department隶属的学院
            School school = ServiceFactory.getService(SchoolService.class,"School").find(schoolId);
            Department desiredDepartment =
                    new Department(id, description, no, remarks,school);
            //将当前记录代表的Department对象添加到desiredDepartments中
            desiredDepartments.add(desiredDepartment);
        }
        JdbcHelper.close(resultSet_select_all,pstmt_select_all,connection);
        return desiredDepartments;
    }

    @Override
    public boolean update(Department department) throws SQLException {
        //获取数据库连接
        Connection connection = JdbcHelper.getConn();
        //准备预编译的语句
        String sql_update =
                "UPDATE department SET description = ?, no = ?, remarks = ?, school_id = ? " +
                        "WHERE id = ?";
        PreparedStatement pstmt_update =
                connection.prepareStatement(sql_update);

        pstmt_update.setString(1, department.getDescription());
        pstmt_update.setString(2, department.getNo());
        pstmt_update.setString(3, department.getRemarks());
        pstmt_update.setInt(4, department.getSchool().getId());
        pstmt_update.setInt(5, department.getId());

        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_update.executeUpdate();

        JdbcHelper.close(pstmt_update,connection);
        return rowAffected > 0;
    }

    @Override
    public boolean delete(Integer id) throws SQLException {
        //获取数据库连接
        Connection connection = JdbcHelper.getConn();
        //准备预编译的语句
        String sql_delete_id = "DELETE FROM department WHERE id = ?";
        PreparedStatement pstmt_delete =
                connection.prepareStatement(sql_delete_id);

        pstmt_delete.setInt(1, id);

        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_delete.executeUpdate();

        JdbcHelper.close(pstmt_delete,connection);
        return rowAffected > 0;
    }

    @Override
    public boolean delete(Department department) throws SQLException {
        return this.delete(department.getId());
    }

    @Override
    public int count(School school) throws SQLException {
        //获取数据库连接
        Connection connection = JdbcHelper.getConn();
        //准备预编译的语句
        String sql_count_by_school_id = "SELECT COUNT(id) as  cnt_by_school_id FROM department WHERE school_id = ?";

        PreparedStatement pstmt_count_by_school_id =
                connection.prepareStatement(sql_count_by_school_id);

        pstmt_count_by_school_id.setInt(1, school.getId());

        int count_by_school_id = 0;
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet_count_by_school_id = pstmt_count_by_school_id.executeQuery();
        if (resultSet_count_by_school_id.next()){
            count_by_school_id = resultSet_count_by_school_id.getInt("cnt_by_school_id");
        }
        JdbcHelper.close(resultSet_count_by_school_id, pstmt_count_by_school_id, connection);
        return count_by_school_id;
    }
}
